package krow.dev.gunpobus.control.database;

import java.util.ArrayList;
import java.util.List;

import krow.dev.gunpobus.Constants.Database;
import krow.dev.gunpobus.Constants.Database.Favorite;
import krow.dev.gunpobus.model.FavoriteItem;
import krow.dev.gunpobus.util.DebugHelper;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DataController {

	private SQLiteDatabase mDatabase;
	
	@SuppressWarnings("deprecation")
	public DataController(Context context) {
		
		DataOpenHelper dataOpenHelper = new DataOpenHelper(context);
		mDatabase = dataOpenHelper.getWritableDatabase();
		mDatabase.setLockingEnabled(true);
	}
	
	public boolean isFavorited(int type, String id){

		boolean isFavorited = false;
		
//		String selection = Database.Favorite.COLUMN_TYPE + " = ? AND " + Database.Favorite.COLUMN_ID + " = '?'";
//		String[] selectionArguments = {String.valueOf(type), id};

		String sql = "SELECT * FROM " + Favorite.TABLE_NAME 
				+ " WHERE " + Favorite.COLUMN_TYPE + " =" + type 
				+ " AND " + Favorite.COLUMN_ID + " ='" + id + "';";
		
		
		Cursor cursor = null;
		try {
			cursor = mDatabase.rawQuery(sql, null);
			
//			cursor = mDatabase.query(
//					Database.Favorite.TABLE_NAME,
//					null,
//					selection, 
//					selectionArguments,
//					null, 
//					null,
//					null);
			if (cursor.moveToFirst()) {
				isFavorited = true;
			}
			cursor.close();
		} catch (Exception e) {
			DebugHelper.e("DataController - isFavorite() exception: " + e.toString());
		} finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
		}

		return isFavorited;
	}
	
	public List<FavoriteItem> getFavoriteItemList(){
		
		List<FavoriteItem> favoriteItemList = new ArrayList<FavoriteItem>();
		
		Cursor cursor = null;
		try {
			cursor = mDatabase.query(
				Database.Favorite.TABLE_NAME,
				null,
				null, 
				null,
				null, 
				null,
				Favorite.COLUMN_TYPE + " asc");

			while (cursor.moveToNext()) {
				FavoriteItem favoriteItem = new FavoriteItem();
				favoriteItem.setType(cursor.getInt(cursor.getColumnIndex(Favorite.COLUMN_TYPE)));
				favoriteItem.setName(cursor.getString(cursor.getColumnIndex(Favorite.COLUMN_NAME)));
				favoriteItem.setId(cursor.getString(cursor.getColumnIndex(Favorite.COLUMN_ID)));
				favoriteItem.setStationSerial(cursor.getString(cursor.getColumnIndex(Favorite.COLUMN_STATION_SERIAL)));
				favoriteItem.setRouteType(cursor.getInt(cursor.getColumnIndex(Favorite.COLUMN_ROUTE_TYPE)));
				favoriteItemList.add(favoriteItem);
			}
			cursor.close();
				
		} catch (Exception e) {
			DebugHelper.e("DataController - getFavoriteItemList() exception: " + e.toString());
		} finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
		}
		return favoriteItemList;
	}
	
	public int getFavoriteItemCount(){
		
		int count = 0;
		
		Cursor cursor = null;
		try {
			cursor = mDatabase.query(
				Database.Favorite.TABLE_NAME,
				null,
				null, 
				null,
				null, 
				null,
				Favorite.COLUMN_TYPE + " asc");

			count = cursor.getCount();
			
			cursor.close();
				
		} catch (Exception e) {
			DebugHelper.e("DataController - getFavoriteItemCount() exception: " + e.toString());
		} finally{
			if(cursor != null && !cursor.isClosed()){
				cursor.close();
			}
		}
		
		return count;
	}
	
	public boolean insertFavoriteItem(int type, String name, String id, String serial, int routeType){
		
		boolean isSucceed = true;
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(Favorite.COLUMN_TYPE, type);
		contentValues.put(Favorite.COLUMN_NAME, name);
		contentValues.put(Favorite.COLUMN_ID, id);
		contentValues.put(Favorite.COLUMN_STATION_SERIAL, serial);
		contentValues.put(Favorite.COLUMN_ROUTE_TYPE, routeType);
		
		try {
			mDatabase.insert(
					Favorite.TABLE_NAME,
					null,
					contentValues);
		} catch (Exception e) {
			isSucceed = false;
		}
		
		return isSucceed;
	}
	
	public boolean deleteFavoriteItem(int type, String id){
		DebugHelper.d("DataController - deleteFavoriteItem()");
		
		boolean isSucceed = true;
		
//		String whereClause = Database.Favorite.COLUMN_TYPE + " = ? AND " + Database.Favorite.COLUMN_ID + " = '?'";
//		String[] whereArguments = {String.valueOf(type), id};
		
		String sql = "DELETE FROM " + Favorite.TABLE_NAME 
				+ " WHERE " + Favorite.COLUMN_TYPE + "=" + type 
				+ " AND " + Favorite.COLUMN_ID + "='" + id + "';";
		DebugHelper.d("DataController - delete query: " + sql);
		
		try {
			mDatabase.execSQL(sql);
			
//			mDatabase.delete(
//					Favorite.TABLE_NAME, 
//					whereClause, 
//					whereArguments);
		} catch (Exception e) {
			DebugHelper.e("DataController - deleteFavoriteItem() exception: " + e.toString());
			isSucceed = false;
		}
		return isSucceed;
	}
}
